
package net.biaji.android.alephclient.data;

import net.biaji.android.alephclient.data.DataContract.Books;
import net.biaji.android.alephclient.data.DataContract.Shelf;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final int DATABASE_VERSION = 1;

    public static final String DATABASE_NAME = "alephclient.db";

    private static final String TYPE_TEXT = " TEXT";

    private static final String TYPE_INT = " INTEGER";

    private static final String NOT_NULL = " NOT NULL";

    private static final String UNIQUE = " UNIQUE";

    private static final String PK = " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL";

    private static final String COMMA_SEP = ",";

    private static final String CREATE_TABLE_BOOK = "CREATE TABLE " + DataContract.Books.TABLE_NAME
            + " (" + DataContract._ID + PK + COMMA_SEP + DataContract.Books.COLUMN_NAME_SN
            + TYPE_TEXT + NOT_NULL + UNIQUE + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_STANDARD_NUM + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_ISBN + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_NAME + TYPE_TEXT + NOT_NULL + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_AUTHORS + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_COVER_URL + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_DESCRIBE + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_PRICE + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_PUBLISHER + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_OTHER_NAME + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_MEDIA_TYPE + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_PUBLISH_DATE + TYPE_TEXT + COMMA_SEP
            + DataContract.Books.COLUMN_NAME_STATUS + TYPE_INT + ")";

    private static final String DROP_TABLE_BOOK = "DROP TABLE IF EXISTS  "
            + DataContract.Books.TABLE_NAME;

    private static final String CREATE_INDEX_BOOK_BOOKSN = "CREATE INDEX booksn ON books (sn ASC)";

    private static final String DROP_INDEX_BOOK_BOOKSN = "DROP INDEX IF EXISTS booksn";

    private static final String CREATE_TABLE_KEYWORDS = "CREATE TABLE "
            + DataContract.Keywords.TABLE_NAME + " (" + DataContract._ID + PK + COMMA_SEP
            + DataContract.Keywords.COLUMN_NAME_KEYWORD + TYPE_TEXT + NOT_NULL + COMMA_SEP
            + DataContract.Keywords.COLUMN_NAME_SEARCHDATE + TYPE_TEXT + COMMA_SEP
            + DataContract.Keywords.COLUMN_NAME_SEARCHTIMES + TYPE_INT + ")";

    private static final String DROP_TABLE_KEYWORDS = "DROP TABLE IF EXISTS  "
            + DataContract.Keywords.TABLE_NAME;

    private static final String CREATE_TABLE_SEARCHMAP = "CREATE TABLE "
            + DataContract.SearchMap.TABLE_NAME + " ("
            + DataContract.SearchMap.COLUMN_NAME_KEYWORD_ID + NOT_NULL + COMMA_SEP
            + DataContract.SearchMap.COLUMN_NAME_BOOK_ID + NOT_NULL + ")";

    private static final String DROP_TABLE_SEARCHMAP = "DROP TABLE IF EXISTS  "
            + DataContract.SearchMap.TABLE_NAME;

    private static final String CREATE_TABLE_SHELF = "CREATE TABLE "
            + DataContract.Shelf.TABLE_NAME + " (" + DataContract._ID + PK + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_BOOKID + TYPE_INT + NOT_NULL + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_EXTEND_CODE + TYPE_TEXT + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_ADD_DATE + TYPE_INT + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_BORROW_DATE + TYPE_INT + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_RETURN_DATE + TYPE_INT + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_RETURN_DATE_EXPECTED + TYPE_INT + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_STATUS + TYPE_INT + COMMA_SEP + " FOREIGN KEY ("
            + DataContract.Shelf.COLUMN_NAME_BOOKID + ") REFERENCES "
            + DataContract.Books.TABLE_NAME + "(" + DataContract._ID + "))";

    private static final String DROP_TABLE_SHELF = "DROP TABLE IF EXISTS "
            + DataContract.Shelf.TABLE_NAME;

    private static final String CREATE_VIEW_BOOKINPOCKET = "CREATE VIEW "
            + DataContract.ViewBooksInPocket.VIEW_NAME + " AS SELECT "
            + DataContract.Books.COLUMN_NAME_NAME + COMMA_SEP + DataContract.Books.COLUMN_NAME_ISBN
            + COMMA_SEP + DataContract.Books.COLUMN_NAME_AUTHORS + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_ADD_DATE + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_BORROW_DATE + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_RETURN_DATE + COMMA_SEP
            + DataContract.Shelf.COLUMN_NAME_RETURN_DATE_EXPECTED + " FROM "
            + DataContract.Books.TABLE_NAME + COMMA_SEP + DataContract.Shelf.TABLE_NAME + " WHERE "
            + Books.TABLE_NAME + "." + Books._ID + "=" + Shelf.TABLE_NAME + "."
            + Shelf.COLUMN_NAME_BOOKID + " AND "
            + Shelf.TABLE_NAME + "." + Shelf.COLUMN_NAME_STATUS + "=" + Shelf.STATUS_INHAND;

    private static final String DROP_VIEW_BOOKINPOCKET = "DROP VIEW"
            + DataContract.ViewBooksInPocket.VIEW_NAME;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_BOOK);
        db.execSQL(CREATE_INDEX_BOOK_BOOKSN);
        db.execSQL(CREATE_TABLE_KEYWORDS);
        db.execSQL(CREATE_TABLE_SEARCHMAP);
        db.execSQL(CREATE_TABLE_SHELF);
        db.execSQL(CREATE_VIEW_BOOKINPOCKET);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_VIEW_BOOKINPOCKET);
        db.execSQL(DROP_INDEX_BOOK_BOOKSN);
        db.execSQL(DROP_TABLE_BOOK);
        db.execSQL(DROP_TABLE_KEYWORDS);
        db.execSQL(DROP_TABLE_SEARCHMAP);
        db.execSQL(DROP_TABLE_SHELF);

        onCreate(db);
    }

}
